/**********************************************************************/
/* InstallPersonalization.SQL - SCHEMA REVAMP VERSION                 */
/*                                                                    */
/* Installs the tables, triggers and stored procedures necessary for  */
/* supporting the personalization feature of ASP.NET                  */
/*                                                                    */
/* InstallCommon.sql must be run before running this file.            */
/*                                                                    */
/* Copyright Microsoft, Inc. 2002                                     */
/* All Rights Reserved.                                               */
/*                                                                    */
/**********************************************************************/

PRINT '------------------------------------------------'
PRINT 'Starting execution of InstallPersonalization.SQL'
PRINT '------------------------------------------------'
GO

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULL_DFLT_ON ON
GO

IF (NOT EXISTS (SELECT SCHEMA_NAME
                FROM INFORMATION_SCHEMA.SCHEMATA
                WHERE SCHEMA_NAME = N'aspnet'))
BEGIN
  RAISERROR('The schema ''aspnet'' cannot be found. Please run InstallCommon.sql first.', 18, 1)
END

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'Applications')))
BEGIN
  RAISERROR('The table ''aspnet.Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'Users')))
BEGIN
  RAISERROR('The table ''aspnet.Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

IF (NOT EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Applications_CreateApplication')))
BEGIN
  RAISERROR('The stored procedure ''aspnet.Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

IF (NOT EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Users_CreateUser')))
BEGIN
  RAISERROR('The stored procedure ''aspnet.Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

IF (NOT EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Users_DeleteUser')))
BEGIN
  RAISERROR('The stored procedure ''aspnet.Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

/***************************************************************************************************************************/
/***************************************************************************************************************************/

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'Paths')))
BEGIN
  PRINT 'Creating the aspnet.Paths table...'
  CREATE TABLE aspnet.Paths (
        ApplicationId    UNIQUEIDENTIFIER    NOT NULL FOREIGN KEY REFERENCES aspnet.Applications(ApplicationId),
        PathId            UNIQUEIDENTIFIER   PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
        Path              NVARCHAR(256)      NOT NULL,
        LoweredPath       NVARCHAR(256)      NOT NULL)
  CREATE UNIQUE CLUSTERED INDEX aspnet_Paths_index ON aspnet.Paths(ApplicationId, LoweredPath)
END


/***************************************************************************************************************************/
/***************************************************************************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Personalization_GetApplicationId')))
DROP PROCEDURE aspnet.Personalization_GetApplicationId
GO

CREATE PROCEDURE aspnet.Personalization_GetApplicationId (
    @ApplicationName NVARCHAR(256),
    @ApplicationId UNIQUEIDENTIFIER OUT)
AS
BEGIN
    SELECT @ApplicationId = ApplicationId FROM aspnet.Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
END
GO

/***************************************************************************************************************************/
/***************************************************************************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Paths_CreatePath')))
DROP PROCEDURE aspnet.Paths_CreatePath
GO

CREATE PROCEDURE aspnet.Paths_CreatePath
    @ApplicationId UNIQUEIDENTIFIER,
    @Path           NVARCHAR(256),
    @PathId         UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    BEGIN TRANSACTION
    IF (NOT EXISTS(SELECT * FROM aspnet.Paths WHERE LoweredPath = LOWER(@Path) AND ApplicationId = @ApplicationId))
    BEGIN
        INSERT aspnet.Paths (ApplicationId, Path, LoweredPath) VALUES (@ApplicationId, @Path, LOWER(@Path))
    END
    COMMIT TRANSACTION
    SELECT @PathId = PathId FROM aspnet.Paths WHERE LOWER(@Path) = LoweredPath AND ApplicationId = @ApplicationId
END
GO

/***************************************************************************************************************************/

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'PersonalizationAllUsers')))
BEGIN
  PRINT 'Creating the aspnet.PersonalizationAllUsers table...'
  CREATE TABLE aspnet.PersonalizationAllUsers (
        PathId            UNIQUEIDENTIFIER PRIMARY KEY FOREIGN KEY REFERENCES aspnet.Paths (PathId),
        PageSettings     IMAGE NOT NULL,
        LastUpdatedDate  DATETIME NOT NULL)
END


IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'PersonalizationPerUser')))
BEGIN
  PRINT 'Creating the aspnet.PersonalizationPerUser table...'
  CREATE TABLE aspnet.PersonalizationPerUser (
        Id               UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
        PathId           UNIQUEIDENTIFIER FOREIGN KEY REFERENCES aspnet.Paths (PathId),
        UserId           UNIQUEIDENTIFIER FOREIGN KEY REFERENCES aspnet.Users (UserId),
        PageSettings     IMAGE NOT NULL,
        LastUpdatedDate  DATETIME NOT NULL)
  CREATE UNIQUE CLUSTERED INDEX aspnet_PersonalizationPerUser_index1 ON [aspnet].[PersonalizationPerUser](PathId,UserId)
  CREATE UNIQUE INDEX aspnet_PersonalizationPerUser_ncindex2 ON [aspnet].[PersonalizationPerUser](UserId,PathId)
END


IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationAllUsers_GetPageSettings')))
DROP PROCEDURE aspnet.PersonalizationAllUsers_GetPageSettings
GO

CREATE PROCEDURE aspnet.PersonalizationAllUsers_GetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path              NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM aspnet.Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT p.PageSettings FROM aspnet.PersonalizationAllUsers p WHERE p.PathId = @PathId
END
GO


IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationAllUsers_ResetPageSettings')))
DROP PROCEDURE aspnet.PersonalizationAllUsers_ResetPageSettings
GO

CREATE PROCEDURE aspnet.PersonalizationAllUsers_ResetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path              NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM aspnet.Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    DELETE FROM aspnet.PersonalizationAllUsers WHERE PathId = @PathId
    RETURN 0
END
GO


IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationAllUsers_SetPageSettings')))
DROP PROCEDURE aspnet.PersonalizationAllUsers_SetPageSettings
GO

CREATE PROCEDURE aspnet.PersonalizationAllUsers_SetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path             NVARCHAR(256),
    @PageSettings     IMAGE,
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC aspnet.Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    SELECT @PathId = u.PathId FROM aspnet.Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        EXEC aspnet.Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
    END

    IF (EXISTS(SELECT PathId FROM aspnet.PersonalizationAllUsers WHERE PathId = @PathId))
        UPDATE aspnet.PersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE PathId = @PathId
    ELSE
        INSERT INTO aspnet.PersonalizationAllUsers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)
    RETURN 0
END
GO


IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationPerUser_GetPageSettings')))
DROP PROCEDURE aspnet.PersonalizationPerUser_GetPageSettings
GO

CREATE PROCEDURE aspnet.PersonalizationPerUser_GetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM aspnet.Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @UserId = u.UserId FROM aspnet.Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        RETURN
    END

    UPDATE   aspnet.Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    SELECT p.PageSettings FROM aspnet.PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
END
GO


IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationPerUser_ResetPageSettings')))
DROP PROCEDURE aspnet.PersonalizationPerUser_ResetPageSettings
GO

CREATE PROCEDURE aspnet.PersonalizationPerUser_ResetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM aspnet.Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @UserId = u.UserId FROM aspnet.Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        RETURN
    END

    UPDATE   aspnet.Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    DELETE FROM aspnet.PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId
    RETURN 0
END
GO


IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationPerUser_SetPageSettings')))
DROP PROCEDURE aspnet.PersonalizationPerUser_SetPageSettings
GO

CREATE PROCEDURE aspnet.PersonalizationPerUser_SetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @PageSettings     IMAGE,
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC aspnet.Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    SELECT @PathId = u.PathId FROM aspnet.Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        EXEC aspnet.Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
    END

    SELECT @UserId = u.UserId FROM aspnet.Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        EXEC aspnet.Users_CreateUser @ApplicationId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
    END

    UPDATE   aspnet.Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    IF (EXISTS(SELECT PathId FROM aspnet.PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId))
        UPDATE aspnet.PersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId
    ELSE
        INSERT INTO aspnet.PersonalizationPerUser(UserId, PathId, PageSettings, LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)
    RETURN 0
END
GO

/*************************************************************/
/* Personalization Administration                            */
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationAdministration_DeleteAllState')))
DROP PROCEDURE aspnet.PersonalizationAdministration_DeleteAllState
GO

CREATE PROCEDURE aspnet.PersonalizationAdministration_DeleteAllState (
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @Count int OUT)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        IF (@AllUsersScope = 1)
            DELETE FROM aspnet.PersonalizationAllUsers
            WHERE PathId IN
               (SELECT Paths.PathId
                FROM aspnet.Paths Paths
                WHERE Paths.ApplicationId = @ApplicationId)
        ELSE
            DELETE FROM aspnet.PersonalizationPerUser
            WHERE PathId IN
               (SELECT Paths.PathId
                FROM aspnet.Paths Paths
                WHERE Paths.ApplicationId = @ApplicationId)

        SELECT @Count = @@ROWCOUNT
    END
END
GO

/*************************************************************/
IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationAdministration_ResetSharedState')))
DROP PROCEDURE aspnet.PersonalizationAdministration_ResetSharedState
GO

CREATE PROCEDURE aspnet.PersonalizationAdministration_ResetSharedState (
    @Count int OUT,
    @ApplicationName NVARCHAR(256),
    @Path NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        DELETE FROM aspnet.PersonalizationAllUsers
        WHERE PathId IN
            (SELECT AllUsers.PathId
             FROM aspnet.PersonalizationAllUsers AllUsers, aspnet.Paths Paths
             WHERE Paths.ApplicationId = @ApplicationId
                   AND AllUsers.PathId = Paths.PathId
                   AND Paths.LoweredPath = LOWER(@Path))

        SELECT @Count = @@ROWCOUNT
    END
END
GO

/*************************************************************/
IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationAdministration_ResetUserState')))
DROP PROCEDURE aspnet.PersonalizationAdministration_ResetUserState
GO

CREATE PROCEDURE aspnet.PersonalizationAdministration_ResetUserState (
    @Count                  int                 OUT,
    @ApplicationName        NVARCHAR(256),
    @InactiveSinceDate      DATETIME            = NULL,
    @UserName               NVARCHAR(256)       = NULL,
    @Path                   NVARCHAR(256)       = NULL)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        DELETE FROM aspnet.PersonalizationPerUser
        WHERE Id IN (SELECT PerUser.Id
                     FROM aspnet.PersonalizationPerUser PerUser, aspnet.Users Users, aspnet.Paths Paths
                     WHERE Paths.ApplicationId = @ApplicationId
                           AND PerUser.UserId = Users.UserId
                           AND PerUser.PathId = Paths.PathId
                           AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
                           AND (@UserName IS NULL OR Users.LoweredUserName = LOWER(@UserName))
                           AND (@Path IS NULL OR Paths.LoweredPath = LOWER(@Path)))

        SELECT @Count = @@ROWCOUNT
    END
END
GO

/*************************************************************/
IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationAdministration_FindState')))
DROP PROCEDURE aspnet.PersonalizationAdministration_FindState
GO

CREATE PROCEDURE aspnet.PersonalizationAdministration_FindState (
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @PageIndex              INT,
    @PageSize               INT,
    @Path NVARCHAR(256) = NULL,
    @UserName NVARCHAR(256) = NULL,
    @InactiveSinceDate DATETIME = NULL)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        RETURN

    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table to store the selected results
    CREATE TABLE #PageIndex (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ItemId UNIQUEIDENTIFIER
    )

    IF (@AllUsersScope = 1)
    BEGIN
        -- Insert into our temp table
        INSERT INTO #PageIndex (ItemId)
        SELECT Paths.PathId
        FROM aspnet.Paths Paths,
             ((SELECT Paths.PathId
               FROM aspnet.PersonalizationAllUsers AllUsers, aspnet.Paths Paths
               WHERE Paths.ApplicationId = @ApplicationId
                      AND AllUsers.PathId = Paths.PathId
                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              ) AS SharedDataPerPath
              FULL OUTER JOIN
              (SELECT DISTINCT Paths.PathId
               FROM aspnet.PersonalizationPerUser PerUser, aspnet.Paths Paths
               WHERE Paths.ApplicationId = @ApplicationId
                      AND PerUser.PathId = Paths.PathId
                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              ) AS UserDataPerPath
              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
             )
        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
        ORDER BY Paths.Path ASC

        SELECT @TotalRecords = @@ROWCOUNT

        SELECT Paths.Path,
               SharedDataPerPath.LastUpdatedDate,
               SharedDataPerPath.SharedDataLength,
               UserDataPerPath.UserDataLength,
               UserDataPerPath.UserCount
        FROM aspnet.Paths Paths,
             ((SELECT PageIndex.ItemId AS PathId,
                      AllUsers.LastUpdatedDate AS LastUpdatedDate,
                      DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
               FROM aspnet.PersonalizationAllUsers AllUsers, #PageIndex PageIndex
               WHERE AllUsers.PathId = PageIndex.ItemId
                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
              ) AS SharedDataPerPath
              FULL OUTER JOIN
              (SELECT PageIndex.ItemId AS PathId,
                      SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
                      COUNT(*) AS UserCount
               FROM aspnet.PersonalizationPerUser PerUser, #PageIndex PageIndex
               WHERE PerUser.PathId = PageIndex.ItemId
                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
               GROUP BY PageIndex.ItemId
              ) AS UserDataPerPath
              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
             )
        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
        ORDER BY Paths.Path ASC
    END
    ELSE
    BEGIN
        -- Insert into our temp table
        INSERT INTO #PageIndex (ItemId)
        SELECT PerUser.Id
        FROM aspnet.PersonalizationPerUser PerUser, aspnet.Users Users, aspnet.Paths Paths
        WHERE Paths.ApplicationId = @ApplicationId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
              AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
        ORDER BY Paths.Path ASC, Users.UserName ASC

        SELECT @TotalRecords = @@ROWCOUNT

        SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
        FROM aspnet.PersonalizationPerUser PerUser, aspnet.Users Users, aspnet.Paths Paths, #PageIndex PageIndex
        WHERE PerUser.Id = PageIndex.ItemId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
        ORDER BY Paths.Path ASC, Users.UserName ASC
    END

    RETURN @TotalRecords
END
GO

/*************************************************************/
IF (EXISTS (SELECT ROUTINE_NAME
              FROM INFORMATION_SCHEMA.ROUTINES
             WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'PersonalizationAdministration_GetCountOfState')))
DROP PROCEDURE aspnet.PersonalizationAdministration_GetCountOfState
GO

CREATE PROCEDURE aspnet.PersonalizationAdministration_GetCountOfState (
    @Count int OUT,
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @Path NVARCHAR(256) = NULL,
    @UserName NVARCHAR(256) = NULL,
    @InactiveSinceDate DATETIME = NULL)
AS
BEGIN

    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC aspnet.Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
        IF (@AllUsersScope = 1)
            SELECT @Count = COUNT(*)
            FROM aspnet.PersonalizationAllUsers AllUsers, aspnet.Paths Paths
            WHERE Paths.ApplicationId = @ApplicationId
                  AND AllUsers.PathId = Paths.PathId
                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
        ELSE
            SELECT @Count = COUNT(*)
            FROM aspnet.PersonalizationPerUser PerUser, aspnet.Users Users, aspnet.Paths Paths
            WHERE Paths.ApplicationId = @ApplicationId
                  AND PerUser.UserId = Users.UserId
                  AND PerUser.PathId = Paths.PathId
                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
                  AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
                  AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
END
GO

/*************************************************************/
/*************************************************************/

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.VIEWS
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'WebPartState_Paths')))
BEGIN
  PRINT 'Creating the aspnet.WebPartState_Paths view...'
  EXEC(N'
  CREATE VIEW [aspnet].[WebPartState_Paths]
  AS SELECT [ApplicationId], [PathId], [Path], [LoweredPath]
  FROM [aspnet].[Paths]
  ')
END

/*************************************************************/
/*************************************************************/

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.VIEWS
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'.WebPartState_Shared')))
BEGIN
  PRINT 'Creating the aspnet.WebPartState_Shared view...'
  EXEC(N'
  CREATE VIEW [aspnet].[WebPartState_Shared]
  AS SELECT [PathId], [DataSize]=DATALENGTH([PageSettings]), [LastUpdatedDate]
  FROM [aspnet].[PersonalizationAllUsers]
  ')
END

/*************************************************************/
/*************************************************************/

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.VIEWS
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'WebPartState_User')))
BEGIN
  PRINT 'Creating the aspnet.WebPartState_User view...'
  EXEC(N'
  CREATE VIEW [aspnet].[WebPartState_User]
  AS SELECT [PathId], [UserId], [DataSize]=DATALENGTH([PageSettings]), [LastUpdatedDate]
  FROM [aspnet].[PersonalizationPerUser]
  ')
END
GO

/*************************************************************/
/*************************************************************/

--
--Create Personalization schema version
--

DECLARE @command nvarchar(4000)
SET @command = 'GRANT EXECUTE ON [aspnet].RegisterSchemaVersion TO ' + QUOTENAME(user)
EXECUTE (@command)
GO

EXEC [aspnet].RegisterSchemaVersion N'Personalization', N'1', 1, 1
GO

/*************************************************************/
/*************************************************************/

--
--Create Personalization roles
--

IF ( NOT EXISTS ( SELECT name
                  FROM sys.database_principals
                  WHERE [type] = 'R'
                  AND name = N'aspnet_Personalization_FullAccess'  ) )
CREATE ROLE aspnet_Personalization_FullAccess

IF ( NOT EXISTS ( SELECT name
                  FROM sys.database_principals
                  WHERE [type] = 'R'
                  AND name = N'aspnet_Personalization_BasicAccess'  ) )
CREATE ROLE aspnet_Personalization_BasicAccess

IF ( NOT EXISTS ( SELECT name
                  FROM sys.database_principals
                  WHERE [type] = 'R'
                  AND name = N'aspnet_Personalization_ReportingAccess'  ) )
CREATE ROLE aspnet_Personalization_ReportingAccess
GO

EXEC sp_addrolemember N'aspnet_Personalization_BasicAccess', N'aspnet_Personalization_FullAccess'
EXEC sp_addrolemember N'aspnet_Personalization_ReportingAccess', N'aspnet_Personalization_FullAccess'
GO

--
--Stored Procedure rights for BasicAccess
--
GRANT EXECUTE ON aspnet.Paths_CreatePath TO aspnet_Personalization_BasicAccess

GRANT EXECUTE ON aspnet.PersonalizationAllUsers_GetPageSettings TO aspnet_Personalization_BasicAccess
GRANT EXECUTE ON aspnet.PersonalizationPerUser_GetPageSettings TO aspnet_Personalization_BasicAccess
GRANT EXECUTE ON aspnet.PersonalizationAllUsers_ResetPageSettings TO aspnet_Personalization_BasicAccess
GRANT EXECUTE ON aspnet.PersonalizationPerUser_ResetPageSettings TO aspnet_Personalization_BasicAccess
GRANT EXECUTE ON aspnet.PersonalizationAllUsers_SetPageSettings TO aspnet_Personalization_BasicAccess
GRANT EXECUTE ON aspnet.PersonalizationPerUser_SetPageSettings TO aspnet_Personalization_BasicAccess

GRANT EXECUTE ON aspnet.Personalization_GetApplicationId TO aspnet_Personalization_BasicAccess

GRANT EXECUTE ON aspnet.CheckSchemaVersion TO aspnet_Personalization_BasicAccess
GRANT EXECUTE ON aspnet.RegisterSchemaVersion TO aspnet_Personalization_BasicAccess
GRANT EXECUTE ON aspnet.UnRegisterSchemaVersion TO aspnet_Personalization_BasicAccess

--
--Stored Procedure rights for ReportingAccess
--
GRANT EXECUTE ON aspnet.PersonalizationAdministration_FindState TO aspnet_Personalization_ReportingAccess
GRANT EXECUTE ON aspnet.PersonalizationAdministration_GetCountOfState TO aspnet_Personalization_ReportingAccess

GRANT EXECUTE ON aspnet.CheckSchemaVersion TO aspnet_Personalization_ReportingAccess
GRANT EXECUTE ON aspnet.RegisterSchemaVersion TO aspnet_Personalization_ReportingAccess
GRANT EXECUTE ON aspnet.UnRegisterSchemaVersion TO aspnet_Personalization_ReportingAccess

--
--Additional stored procedure rights for FullAccess
--
GRANT EXECUTE ON aspnet.PersonalizationAdministration_ResetUserState TO aspnet_Personalization_FullAccess
GRANT EXECUTE ON aspnet.PersonalizationAdministration_ResetSharedState TO aspnet_Personalization_FullAccess
GRANT EXECUTE ON aspnet.PersonalizationAdministration_DeleteAllState TO aspnet_Personalization_FullAccess

--
--View rights
--
GRANT SELECT ON aspnet.ApplicationsView TO aspnet_Personalization_ReportingAccess
GRANT SELECT ON aspnet.UsersView TO aspnet_Personalization_ReportingAccess

GRANT SELECT ON aspnet.WebPartState_Paths TO aspnet_Personalization_ReportingAccess
GRANT SELECT ON aspnet.WebPartState_Shared TO aspnet_Personalization_ReportingAccess
GRANT SELECT ON aspnet.WebPartState_User TO aspnet_Personalization_ReportingAccess
GO
-------------------------------------------------------------------------
--- Version specific install
-------------------------------------------------------------------------

DECLARE @ver INT
DECLARE @version NCHAR(100)
DECLARE @dot INT
DECLARE @hyphen INT
DECLARE @SqlToExec NCHAR(400)

SELECT @ver = 8
SELECT @version = @@Version
SELECT @hyphen  = CHARINDEX(N' - ', @version)
IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
BEGIN
    SELECT @hyphen = @hyphen + 3
    SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
    IF (NOT(@dot IS NULL) AND @dot > @hyphen)
    BEGIN
        SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
        SELECT @ver     = CONVERT(INT, @version)
    END
END

--IF (@ver >= 8)
--BEGIN
--    EXEC sp_tableoption N'aspnet_PersonalizationAllUsers', 'text in row', 6000
--    EXEC sp_tableoption N'aspnet_PersonalizationPerUser', 'text in row', 6000
--END
--GO

/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/

DECLARE @command nvarchar(4000)
SET @command = 'REVOKE EXECUTE ON [aspnet].RegisterSchemaVersion FROM ' + QUOTENAME(user)
EXECUTE (@command)
GO

PRINT '-------------------------------------------------'
PRINT 'Completed execution of InstallPersonalization.SQL'
PRINT '-------------------------------------------------'
